Back to Main Menu

Repairing Spatial Data

When attempting to import GIS data into a SQL Server, there exist several constraints enforced on the data being loaded for integrity purposes. These constraints may generate error messages that prevent data from being exported properly. As part of Assetic’s database configuration, polygon data exported into the system is subject to similar rigorous standards to prevent the import of broken or ambiguous polygons which lack a coherent and clearly defined structure. Unfortunately, many WKT (Well Known Text) exporting extensions fail in meeting these standards, producing polygon datasets that are incompatible with Assetic’s database standards. As a workaround to ‘broken’ polygons, Assetic recommends running an automated repair method such as Prepair, PostGIS 2.0’s ST_MakeValid() function, or other such tools.

Using the Prepair Tool

The Prepair tool can be downloaded from the following link:

https://github.com/tudelft3d/prepair/releases/download/v0.7.1/prepair-x64-0.7.1.zip

 

The file should be downloaded and extracted into a folder of choice. The tool can be executed via the following set of steps:

 

Launch the Windows Command Shell\Command Prompt. This can be accessed by running ‘cmd’ from the start menu.

 

Navigate to the folder into which the contents of the Prepair folder were extracted. This can be achieved by running the command ‘cd <folder path>’, without the quotation marks, and where <folder path> is the file path to the folder containing the contents.

 

From here, typing in the command ‘prepair’ will give a quick overview of the module’s functionality:

 

The whole suite of functionality is beyond the scope of this document. The focus of this article covers the ‘prepair –wkt’ option, which takes a Polygon input and performs shape correction to return a corrected Multipolygon output. To run it, the user runs the following command:

 

‘prepair –wkt “POLYGON” ‘, where POLYGON is simply the Polygon to be corrected.

Important: It is mandatory to encase the word polygon in double quotation marks.

A sample execution is provided below:

 

The Multipolygon output is suitable for directly exporting into Assetic’s systems.

Handling large polygons

On occasion, the need to process exceptionally large and intricate polygons may arise. Such polygon data is usually characterized by having many points at a high degree of measuring precision, leading to many coordinate points, with each coordinate point having many decimal points. The Windows PowerShell has an inherent limitation of inputs of up to 8191 characters, and in many cases, large polygons will quickly meet or exceed the input limitations. It is not always feasible or simple to truncate the data to be repaired. In such cases, the following set of procedures is the recommended workaround.

 

The Prepair tool has the functionality of repairing Polygon data from text files, which can be utilized to work around the PowerShell limitation.

 

Paste the polygon data into a text file and save it into the same location as the Prepair folder.

 

As can be observed in the screenshot, the quotation marks are unnecessary when loading polygon data through a .txt file.

 

Next, running the following command on the command line:

 

Prepair -f <TEXTFILE>, where <TEXTFILE> is the .txt file containing the target polygon data.

 

Please note that the above method only repairs one polygon per .txt file and so users should take care to avoid storing multiple lines of Polygons in the .txt file to attempt to bulk repair many polygons at once.

Bulk Repair Polygons

To perform a bulk repair of multiple polygons at once, a scripted approach is recommended. A sample script utilizing Python and the subprocess module is available as a reference below:

NOTE  To learn more about the Python programming language, and for additional information on available Assetic Python packages, please see the Assetic Python SDK - Quick Start article.

This sample script parses an auto-generated error file (.csv) created by the Assetic Cloud Data Exchange if import errors occur during a spatial import. The repaired spatial data is appended to additional columns with the suffix "_Fixed" in the output file.

 

The sample script also assumes that the standard csv template for spatial imports from the Data Exchange Import Wizard (Mapping -> Asset Spatial) is being used. This is for the purposes of reading and parsing the error file data using the standard template column headers ("Asset ID", "Point", "Polygon", "Line").

 

If using the sample script the file paths found on lines 127, 130, and 131 must be modified. These paths point towards the directory of the extracted 'Prepair' tool, the input file path (.csv), and a nominated output file (.csv) file path. 

NOTE  

If the output file is used for a subsequent Data Exchange import, care should be taken during the mapping steps of the Import Wizard to change the mapping to the new column containing the repaired spatial data.

 

  1. from subprocess import  Popen, PIPE
  2. import pandas as pd
  3. import os
  4. import re
  5.   
  6.   
  7. """
  8.     Assetic.BulkPrepair.py
  9.   
  10.     This script demonstrates how to repair broken spatial data in Well Known Text (WKT) format using the Prepair tool.
  11.     The script parses the auto-generated error file (.csv) from the Assetic Cloud Data Exchange import: 
  12.     Mapping -> Asset Spatial
  13.   
  14.     Errors are filtered and repaired depending on the type of the WKT (i.e Polygons, Multi-Polygons and Linestrings).
  15.         - Polygons and Multi-Polygons are repaired using the Prepair utility
  16.         - Linestrings are attempted to be repaired by checking for consecutive duplicate Points
  17.     The repaired data is then appended as new columns when saved to the .csv output file.
  18.   
  19.     Prepair download link: https://github.com/tudelft3d/prepair/releases  
  20.     Full documentation: https://assetic.zendesk.com/hc/en-us/articles/360000516615
  21.     Author: Shien Jinn Sam (Assetic)
  22. """
  23.   
  24.   
  25. def repair_linestring(broken_linestrings):
  26.   # isolate points from linestring in order to compare and remove consecutive duplicates
  27.     fixed_linestrings =  []
  28.   
  29.   for linestring in broken_linestrings:
  30.   
  31.   # find if linestring or multilinestring through WKT prefix
  32.         wkt_prefix = str.split(linestring,  "(")[0].strip()
  33.         valid_prefix =  ['LINESTRING',  'MULTILINESTRING']
  34.   if wkt_prefix.upper()  not  in valid_prefix:
  35.   raise  ValueError(f"Invalid Line Prefix encountered: {wkt_prefix}.\n"
  36.                              f"Valid Values:{valid_prefix}")
  37.   
  38.   if wkt_prefix.upper()  ==  'MULTILINESTRING':
  39.   # remove wkt prefix and strip enclosing outer parenthesis
  40.             wkt_content = linestring[len(wkt_prefix):].strip()[1:-1]
  41.   # split wkt on parentheses to get list of each line that comprises the multiline
  42.             wkt_multilines_list = re.findall(r"\((.*?)\)", wkt_content)
  43.   
  44.   # parse each line individually to repair
  45.             fixed_sub_lines = list()
  46.   for line in wkt_multilines_list:
  47.   # parse the line and remove repeated points
  48.                 parsed_line = _del_repeated_linestring_pnts(line)
  49.   # put the linestring back inside parenthesis and append to list
  50.                 fixed_sub_lines.append("("  + parsed_line +  ")")
  51.   
  52.   # put all lines from the multiline back into a single WKT string
  53.             fixed_multiline =  "MULTILINESTRING("  + str(", ".join(fixed_sub_lines))  +  ")"
  54.             fixed_linestrings.append(fixed_multiline)
  55.   
  56.   elif wkt_prefix.upper()  ==  'LINESTRING':
  57.   # Remove leading "LineString" string and brackets for just comma separated points
  58.   # Also Strip leading and trailing spaces, and separate each point into a list
  59.             wkt_content = linestring[linestring.find("(")  +  1:]
  60.             wkt_content = wkt_content[0:wkt_content.find(")"):]
  61.             parsed_line = _del_repeated_linestring_pnts(wkt_content)
  62.   # reconstruct LINESTRING in valid WKT format
  63.             fixed_linestrings.append("LINESTRING("  + parsed_line +  ")")
  64.   return fixed_linestrings
  65.   
  66.   
  67. def _del_repeated_linestring_pnts(wkt_string: str):
  68.   """
  69.     Accepts a WKT Linestring in a str format, and removes any vertices (points)
  70.     that do not adhere to duplicate vertex rules.
  71.   
  72.     To adhere to OGC standards, the Linestring is split into individual points
  73.     and the points are compared for duplicates. A well formatted Linestring
  74.     will not have consecutive duplicate vertices (points), and will also not
  75.     have overlapping segments where duplicate vertices with a single vertex is
  76.     between them.
  77.     """
  78.   # seperate wkt string into a list of points for comparison
  79.     raw_points_list =  [item.strip()  for item in wkt_string.split(',')]
  80.   
  81.   # Get total number of points, minus 1 to adjust for 0 index
  82.     total_points = len(raw_points_list)
  83.   
  84.   # Skip linestrings with only 2 points, deleting 1 would make it no longer a Linestring
  85.   if total_points <=  2:
  86.   return wkt_string
  87.   
  88.   # Start from index 0 and iterate over full points list to check for vertex OGC compliance
  89.     i =  0
  90.   while i <  (total_points -  1):
  91.   if total_points <  2:
  92.   print("Invalid LINESTRING detected! Repaired linestring is less than 2 vertices.")
  93.   
  94.   # check immediate neighbor duplicates
  95.   if raw_points_list[i]  == raw_points_list[i +  1]:
  96.   del raw_points_list[i +  1]
  97.             total_points -=  1
  98.   # also move i iterator back 1 to allow regressive leapfrog check after change
  99.   if i >  0:
  100.                 i -=  1
  101.   continue
  102.   
  103.   # check for duplicate line segment, leapfrog 1 point and check if that point is duplicate
  104.         lf = i +  2
  105.   if lf <=  (total_points -  1):
  106.   if raw_points_list[i]  == raw_points_list[lf]:
  107.   del raw_points_list[lf]
  108.                 total_points -=  1
  109.   continue
  110.   # iterate and continue checks
  111.         i +=  1
  112.   
  113.   # join the points back to a str and return
  114.     rejoined_points =  ", ".join(raw_points_list)
  115.   return rejoined_points
  116.   
  117.   
  118. def prepair_polygons(prepair_dir, broken_polygons):
  119.     path = prepair_dir
  120.   
  121.   # initiate empty list for storing the fixed polygons
  122.     outputs =  []
  123.   # create loop
  124.   for polygon in broken_polygons:
  125.   try:
  126.   # defaults to using command line for repairing but also check the polygon isn't bigger than 32768 characters
  127.             command =  ['prepair',  '--wkt', polygon]
  128.   # collect output and error data as byte types
  129.             process =  Popen(command, cwd=path, shell=True, stdin=PIPE, stdout=PIPE, stderr=PIPE)
  130.   out, err = process.communicate()
  131.   except  FileNotFoundError  as e:
  132.   # Command line limit on CreateProcess() function has 32768 char limit for args
  133.   # Handle this and treat it as the command line is too long
  134.   if len(polygon)  >=  32768:
  135.   out  =  None
  136.                 err =  'The command line is too long.'.encode(encoding='ascii')
  137.   else:
  138.   # unknown error not yet handled, rethrow
  139.   raise e
  140.   
  141.   # If polygon is too large, create a .txt file for loading to overcome Windows command shell limitations.
  142.   # This is computationally expensive, but for all practical purposes, it will run relatively quickly.
  143.   # Only polygons>8191 characters/points will require this.
  144.   if err.decode("ascii").strip()  ==  'The command line is too long.':
  145.   # Write and load polygon from a .txt file to sidestep windows shell command character input limitation
  146.   # The previous polygon will be overwritten in the next cycle of the for loop
  147.             f = open(os.path.join(path,  'Polygon.txt'),  'w')
  148.             f.write(polygon)
  149.             f.close()
  150.   # specify command line command
  151.             command =  ['prepair.exe',  '-f',  "polygon.txt"]
  152.   # initiate child process
  153.             process =  Popen(command, cwd=path, shell=True, stdin=PIPE, stdout=PIPE, stderr=PIPE)
  154.   out, err = process.communicate()  # collect output and error data as byte types
  155.         outputs.append(out.decode("ascii").strip())  # append decoded bytes and remove trailing whitespace
  156.   return outputs
  157.   
  158.   
  159. def main(prepair_dir, input_file_path, output_file_path):
  160.   # Read data from .csv error file into a pandas datatable
  161.     errors = pd.read_csv(input_file_path, dtype=str)
  162.   
  163.   # create a list to hold the output dataframes
  164.   fixed  = list()
  165.   
  166.   # 1. Get Polygons/Multipolygons data and pass the 'Polygon' WKT column to Prepair
  167.     broken_polygons = errors[~errors["Polygon"].isnull()].copy()
  168.     polygons = broken_polygons["Polygon"]
  169.   if  not polygons.empty:
  170.         fixed_polygons = prepair_polygons(prepair_dir, polygons)
  171.         broken_polygons["Polygon_Fixed"]  = fixed_polygons
  172.   fixed.append(broken_polygons)
  173.   
  174.   # 2. Get Linestring and remove consecutive duplicate Points (common issue) MULTILINESTRING rows are not handled
  175.     broken_linestring = errors[~errors["Line"].isnull()].copy()
  176.   
  177.     linestrings = broken_linestring["Line"]
  178.   if  not linestrings.empty:
  179.         fixed_linestrings = repair_linestring(linestrings)
  180.         broken_linestring["Line_Fixed"]  = fixed_linestrings
  181.   fixed.append(broken_linestring)
  182.   
  183.     data_out = pd.DataFrame()
  184.   # if both Polygons and Lines have been repaired, merge the fixed data together
  185.   if len(fixed)  >  1:
  186.         data_out = pd.concat(fixed)
  187.   elif len(fixed)  ==  1:
  188.         data_out =  fixed[0]
  189.   
  190.   # Write to the output file
  191.   if  not data_out.empty:
  192.         data_out.to_csv(output_file_path, index=False)
  193.   else:
  194.   print("Output pandas dataframe is empty. No data has been repaired.")
  195.   
  196.   
  197. # This will run the method main() to start the repair process
  198. # Please update the file paths below for your prepair directory and input/output files.
  199. if __name__ ==  "__main__":
  200.   # Set prepair directory, download link @https://github.com/tudelft3d/prepair/releases
  201.     prepair_directory =  "D:/temp/prepair-x64-0.7.1"
  202.   
  203.   # Set paths here for the input and output CSV files
  204.     input_file =  "C:/temp/broken_data.csv"
  205.     output_file =  "C:/temp/fixed_data.csv"
  206.   
  207.   # Run the repair process
  208.     main(prepair_dir=prepair_directory,
  209.          input_file_path=input_file,
  210.          output_file_path=output_file)